/////// This code produces data for dynamic plots in figures 8-10   ////////////
global raw "Replication_Package\Data\raw\"
global processed "Replication_Package\Data\processed\"
global output "Replication_Package\Output\"



//////////////////////////////////////////////////////////////////////////////// 
////// find losses as of April 5, 2022 
use "${processed}index_price_april",clear 
sort date 
foreach x of var treasury*  rmbs_etf  {
	gen d_`x' = `x'[_N]/`x'[1]
}
keep  d_* 
duplicates drop
gen rmbs_multiplier = (1-d_rmbs_etf)/(1-d_treasury)

gen quarter = 1 
// merge to get 2022Q1 call reports 
merge 1:m quarter using "${raw}callreport_2022q1_clean" 
keep if _merge==3
drop _merge 
replace total_asset = total_asset*1000000 // convert it back into thousands

// treasury 
egen treasury = rowtotal(treasury_htm_amortize treasury_hfs_fair)
egen rmbs = rowtotal(agency_rmbs_htm_amortize  agency_rmbs_hfs_fair other_rmbs_htm_amortize other_rmbs_hfs_fair)
egen cmbs = rowtotal(agency_cmbs_htm_amortize agency_cmbs_hfs_fair other_cmbs_htm_amortize other_cmbs_hfs_fair)
egen abs = rowtotal(abs_htm_amortize abs_hfs_fair)

foreach x of var *_3mo *_1yr *_3yr *_5yr *_15yr *_20yr {
replace `x'=0 if missing(`x')
}
gen loss = (treasury_3mo+treasury_1yr+all_other_loan_3mo+all_other_loan_1yr)*(1-d_treasury_1yr)+ (treasury_3yr+all_other_loan_3yr)*(1-d_treasury_1to3)+(treasury_5yr+all_other_loan_5yr)*(1-d_treasury_3to5)+(treasury_15yr+all_other_loan_15yr)*(1-d_treasury_7to10)+(treasury_20yr+all_other_loan_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2) /// non-rmbs securities & non-mortgage loans
+(rmbs_3mo+rmbs_1yr+reloan1to4_3mo+reloan1to4_1yr)*(1-d_treasury_1yr)*rmbs_multiplier+ (rmbs_3yr+reloan1to4_3yr)*(1-d_treasury_1to3)*rmbs_multiplier+(rmbs_5yr+reloan1to4_5yr)*(1-d_treasury_3to5)*rmbs_multiplier+(rmbs_15yr+reloan1to4_15yr)*(1-d_treasury_7to10)*rmbs_multiplier+(rmbs_20yr+reloan1to4_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2)*rmbs_multiplier /// rmbs and mortgage 
+(other_rmbs_3yrless)*(1-(d_treasury_1yr+d_treasury_1to3)/2)*rmbs_multiplier + (other_rmbs_3yrmore)*(1-(d_treasury_3to5+d_treasury_7to10+d_treasury_10to20+d_treasury_20plus)/4)*rmbs_multiplier

save "${processed}analysis_loss_dynamic_april",replace 

///////////////////////// dynamic losses //////////////////////
// find dynamic losses 
use "${raw}callreport_2022q1_clean" ,clear 
expand 9
bys idrssd: gen date = 248+_n 
save "${processed}callreport_expanded",replace 

// find price changes since 2022Q1
use "${processed}index_price",clear 
foreach x of var treasury*  rmbs_etf fedfunds {
gen `x'_2022q1 = `x' if date == 248 //2022Q1 
sort `x'_2022q1 
replace `x'_2022q1 = `x'_2022q1[1]
gen d_`x' = `x'/`x'_2022q1
}
format date %tq
sort date 
keep if date>248
keep date d_* 
gen rmbs_multiplier = (1-d_rmbs_etf)/(1-d_treasury)

// merge to get 2022Q1 call reports 
merge 1:m date using "${processed}callreport_expanded" 
keep if _merge==3
drop _merge 
replace total_asset = total_asset*1000000 // convert it back into thousands

// treasury 
egen treasury = rowtotal(treasury_htm_amortize treasury_hfs_fair)
egen rmbs = rowtotal(agency_rmbs_htm_amortize  agency_rmbs_hfs_fair other_rmbs_htm_amortize other_rmbs_hfs_fair)
egen cmbs = rowtotal(agency_cmbs_htm_amortize agency_cmbs_hfs_fair other_cmbs_htm_amortize other_cmbs_hfs_fair)
egen abs = rowtotal(abs_htm_amortize abs_hfs_fair)

foreach x of var *_3mo *_1yr *_3yr *_5yr *_15yr *_20yr {
replace `x'=0 if missing(`x')
}
gen loss = (treasury_3mo+treasury_1yr+all_other_loan_3mo+all_other_loan_1yr)*(1-d_treasury_1yr)+ (treasury_3yr+all_other_loan_3yr)*(1-d_treasury_1to3)+(treasury_5yr+all_other_loan_5yr)*(1-d_treasury_3to5)+(treasury_15yr+all_other_loan_15yr)*(1-d_treasury_7to10)+(treasury_20yr+all_other_loan_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2) /// non-rmbs securities & non-mortgage loans
+(rmbs_3mo+rmbs_1yr+reloan1to4_3mo+reloan1to4_1yr)*(1-d_treasury_1yr)*rmbs_multiplier+ (rmbs_3yr+reloan1to4_3yr)*(1-d_treasury_1to3)*rmbs_multiplier+(rmbs_5yr+reloan1to4_5yr)*(1-d_treasury_3to5)*rmbs_multiplier+(rmbs_15yr+reloan1to4_15yr)*(1-d_treasury_7to10)*rmbs_multiplier+(rmbs_20yr+reloan1to4_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2)*rmbs_multiplier /// rmbs and mortgage 
+(other_rmbs_3yrless)*(1-(d_treasury_1yr+d_treasury_1to3)/2)*rmbs_multiplier + (other_rmbs_3yrmore)*(1-(d_treasury_3to5+d_treasury_7to10+d_treasury_10to20+d_treasury_20plus)/4)*rmbs_multiplier

save "${processed}analysis_loss_dynamic",replace 

erase "${processed}callreport_expanded.dta"
///////////////////////////////////////////////////////////////////////////////
/// output for figures 8-10

use "${processed}analysis_loss_dynamic",clear

gen cre_loan = reloan-reloan_residential1to4 
//gen date = mdy(4,5,2022)
forvalue x = 0(10)20{
gen cre_loss = 0.3*cre_loan*`x'/100 // 70% recovery rate 

// with only cre default or extreme case 
gen dd_equity = total_equity-cre_loss 
gen mm_dd_equity = total_equity-cre_loss-loss

gen neg_dd_equity = (dd_equity<0)
gen neg_mm_equity = (mm_dd_equity<0)

egen agg_default_loss_`x' = sum(cre_loss), by(date)
egen count_neg_extreme_dd_`x' = sum(neg_dd_equity), by(date)
egen count_neg_extreme_mm_`x' = sum(neg_mm_equity), by(date)
egen agg_asset_extreme_neg_dd_`x' = sum(neg_dd_equity*total_asset), by(date)
egen agg_asset_extreme_neg_mm_`x' = sum(neg_mm_equity*total_asset), by(date)

drop  dd_equity mm_dd_equity neg_dd_equity neg_mm_equity

// with 50% insured depositor run 
gen dd_insured_coverage = total_asset-cre_loss-0.5*uninsured_deposit-insured_deposit 
gen mm_dd_insured_coverage = total_asset-cre_loss-loss-0.5*uninsured_deposit-insured_deposit 

gen neg_dd_insured_coverage = (dd_insured_coverage<0)
gen neg_mm_insured_coverage = (mm_dd_insured_coverage<0)

egen count_neg_50run_dd_`x' = sum(neg_dd_insured_coverage), by(date)
egen count_neg_50run_mm_`x' = sum(neg_mm_insured_coverage), by(date)
egen agg_asset_50run_neg_dd_`x' = sum(neg_dd_insured_coverage*total_asset), by(date)
egen agg_asset_50run_neg_mm_`x' = sum(neg_mm_insured_coverage*total_asset), by(date)

drop  dd_insured_coverage mm_dd_insured_coverage neg_dd_insured_coverage neg_mm_insured_coverage

// with 100% insured depositor run 
gen dd_insured_coverage = total_asset-cre_loss-uninsured_deposit-insured_deposit 
gen mm_dd_insured_coverage = total_asset-cre_loss-loss-uninsured_deposit-insured_deposit 

gen neg_dd_insured_coverage = (dd_insured_coverage<0)
gen neg_mm_insured_coverage = (mm_dd_insured_coverage<0)

egen count_neg_100run_dd_`x' = sum(neg_dd_insured_coverage), by(date)
egen count_neg_100run_mm_`x' = sum(neg_mm_insured_coverage), by(date)
egen agg_asset_100run_neg_dd_`x' = sum(neg_dd_insured_coverage*total_asset), by(date)
egen agg_asset_100run_neg_mm_`x' = sum(neg_mm_insured_coverage*total_asset), by(date)

drop cre_loss dd_insured_coverage mm_dd_insured_coverage neg_dd_insured_coverage neg_mm_insured_coverage


}



keep  date count_neg_* agg_asset_* 
duplicates drop

reshape long  count_neg_extreme_dd_ count_neg_extreme_mm_ agg_asset_extreme_neg_dd_ agg_asset_extreme_neg_mm_   count_neg_50run_dd_ count_neg_50run_mm_ agg_asset_50run_neg_dd_ agg_asset_50run_neg_mm_   count_neg_100run_dd_ count_neg_100run_mm_ agg_asset_100run_neg_dd_ agg_asset_100run_neg_mm_  , i(date) j(default)


foreach x of var agg_asset_* {
replace `x' = `x'/1000000000 // trillion
}

ren default CRE_Distress 
drop *_dd_
ren count_neg_extreme_mm Number_Insolvent_Bank_Extreme
ren agg_asset_extreme_neg_mm_ Asset_Insolvent_Bank_Extreme

ren count_neg_50run_mm Number_Insolvent_Bank_IDCR_50
ren agg_asset_50run_neg_mm_ Asset_Insolvent_Bank_IDCR_50

ren count_neg_100run_mm Number_Insolvent_Bank_IDCR_100
ren agg_asset_100run_neg_mm_ Asset_Insolvent_Bank_IDCR_100

gen year = year(dofq(date))
gen quarter = quarter(dofq(date))
drop date
tostring year quarter,replace 
gen date = year+"q"+quarter
drop year quarter 
order date 
save temp,replace 



use "${processed}analysis_loss_dynamic_april",clear

gen cre_loan = reloan-reloan_residential1to4 
gen date = mdy(4,5,2022)
forvalue x = 0(10)20{
gen cre_loss = 0.3*cre_loan*`x'/100 // 70% recovery rate 

// with only cre default or extreme case 
gen dd_equity = total_equity-cre_loss 
gen mm_dd_equity = total_equity-cre_loss-loss

gen neg_dd_equity = (dd_equity<0)
gen neg_mm_equity = (mm_dd_equity<0)

egen agg_default_loss_`x' = sum(cre_loss), by(date)
egen count_neg_extreme_dd_`x' = sum(neg_dd_equity), by(date)
egen count_neg_extreme_mm_`x' = sum(neg_mm_equity), by(date)
egen agg_asset_extreme_neg_dd_`x' = sum(neg_dd_equity*total_asset), by(date)
egen agg_asset_extreme_neg_mm_`x' = sum(neg_mm_equity*total_asset), by(date)

drop  dd_equity mm_dd_equity neg_dd_equity neg_mm_equity

// with 50% insured depositor run 
gen dd_insured_coverage = total_asset-cre_loss-0.5*uninsured_deposit-insured_deposit 
gen mm_dd_insured_coverage = total_asset-cre_loss-loss-0.5*uninsured_deposit-insured_deposit 

gen neg_dd_insured_coverage = (dd_insured_coverage<0)
gen neg_mm_insured_coverage = (mm_dd_insured_coverage<0)

egen count_neg_50run_dd_`x' = sum(neg_dd_insured_coverage), by(date)
egen count_neg_50run_mm_`x' = sum(neg_mm_insured_coverage), by(date)
egen agg_asset_50run_neg_dd_`x' = sum(neg_dd_insured_coverage*total_asset), by(date)
egen agg_asset_50run_neg_mm_`x' = sum(neg_mm_insured_coverage*total_asset), by(date)

drop  dd_insured_coverage mm_dd_insured_coverage neg_dd_insured_coverage neg_mm_insured_coverage

// with 100% insured depositor run 
gen dd_insured_coverage = total_asset-cre_loss-uninsured_deposit-insured_deposit 
gen mm_dd_insured_coverage = total_asset-cre_loss-loss-uninsured_deposit-insured_deposit 

gen neg_dd_insured_coverage = (dd_insured_coverage<0)
gen neg_mm_insured_coverage = (mm_dd_insured_coverage<0)

egen count_neg_100run_dd_`x' = sum(neg_dd_insured_coverage), by(date)
egen count_neg_100run_mm_`x' = sum(neg_mm_insured_coverage), by(date)
egen agg_asset_100run_neg_dd_`x' = sum(neg_dd_insured_coverage*total_asset), by(date)
egen agg_asset_100run_neg_mm_`x' = sum(neg_mm_insured_coverage*total_asset), by(date)

drop cre_loss dd_insured_coverage mm_dd_insured_coverage neg_dd_insured_coverage neg_mm_insured_coverage


}



keep  date count_neg_* agg_asset_* 
duplicates drop

reshape long  count_neg_extreme_dd_ count_neg_extreme_mm_ agg_asset_extreme_neg_dd_ agg_asset_extreme_neg_mm_   count_neg_50run_dd_ count_neg_50run_mm_ agg_asset_50run_neg_dd_ agg_asset_50run_neg_mm_   count_neg_100run_dd_ count_neg_100run_mm_ agg_asset_100run_neg_dd_ agg_asset_100run_neg_mm_  , i(date) j(default)


foreach x of var agg_asset_* {
replace `x' = `x'/1000000000 // trillion
}

ren default CRE_Distress 
drop *_dd_
ren count_neg_extreme_mm Number_Insolvent_Bank_Extreme
ren agg_asset_extreme_neg_mm_ Asset_Insolvent_Bank_Extreme

ren count_neg_50run_mm Number_Insolvent_Bank_IDCR_50
ren agg_asset_50run_neg_mm_ Asset_Insolvent_Bank_IDCR_50

ren count_neg_100run_mm Number_Insolvent_Bank_IDCR_100
ren agg_asset_100run_neg_mm_ Asset_Insolvent_Bank_IDCR_100
drop date 
gen date = "2022April5"
order date 
append using temp 
erase temp.dta
export excel using "${processed}Figure_8_10.xls", firstrow(variables)
